use chrono::{DateTime, Utc};
use indoc::indoc;
use rusqlite::{Connection, Row, named_params, params};

use crate::podcast::episode::EpisodeNoId;

use super::{PodcastDBId, convert_date};

/// A struct representing a episode in a podcast in the database
#[derive(Debug, Clone)]
pub struct EpisodeDB {
    pub id: PodcastDBId,
    pub pod_id: PodcastDBId,
    pub title: String,
    pub url: String,
    pub guid: String,
    pub description: String,
    pub pubdate: Option<DateTime<Utc>>,
    pub duration: Option<i64>,
    pub played: bool,
    #[allow(dead_code)]
    pub hidden: bool,
    pub last_position: Option<i64>,
    pub image_url: Option<String>,
}

impl EpisodeDB {
    /// Try to convert a given row to a [`EpisodeDB`] instance, using column names to resolve the values
    #[allow(dead_code)]
    pub fn try_from_row_named(row: &Row<'_>) -> Result<Self, rusqlite::Error> {
        // NOTE: all the names in "get" below are the *column names* as defined in migrations/001.sql#table_episodes (pseudo link)
        Ok(Self {
            id: row.get("id")?,
            pod_id: row.get("podcast_id")?,
            title: row.get("title")?,
            url: row.get("url")?,
            guid: row.get::<_, Option<String>>("guid")?.unwrap_or_default(),
            description: row.get("description")?,
            pubdate: convert_date(&row.get("pubdate")),
            duration: row.get("duration")?,
            played: row.get("played")?,
            hidden: row.get("hidden")?,
            last_position: row.get("last_position")?,
            image_url: row.get("image_url")?,
        })
    }

    /// Try to convert a given row to a [`EpisodeDB`] instance, using column names to resolve the values (with renamed id because of conflicts)
    pub fn try_from_row_named_alias_id(row: &Row<'_>) -> Result<Self, rusqlite::Error> {
        // NOTE: all the names in "get" below are the *column names* as defined in migrations/001.sql#table_episodes (pseudo link)
        Ok(Self {
            id: row.get("epid")?,
            pod_id: row.get("podcast_id")?,
            title: row.get("title")?,
            url: row.get("url")?,
            guid: row.get::<_, Option<String>>("guid")?.unwrap_or_default(),
            description: row.get("description")?,
            pubdate: convert_date(&row.get("pubdate")),
            duration: row.get("duration")?,
            played: row.get("played")?,
            hidden: row.get("hidden")?,
            last_position: row.get("last_position")?,
            image_url: row.get("image_url")?,
        })
    }
}

/// A struct representing a episode in a podcast in the database to be inserted
///
/// This is required as some fields are auto-generated by the database compared to [`EpisodeDB`]
#[derive(Debug, Clone)]
pub struct EpisodeDBInsertable<'a> {
    // generated by the database
    // pub id: PodcastDBId,
    pub pod_id: PodcastDBId,
    pub title: &'a str,
    pub url: &'a str,
    pub guid: &'a str,
    pub description: &'a str,
    pub pubdate: Option<DateTime<Utc>>,
    pub duration: Option<i64>,
    pub played: bool,
    pub hidden: bool,
    pub last_position: Option<i64>,
    pub image_url: Option<&'a str>,
}

impl<'a> EpisodeDBInsertable<'a> {
    /// Basically the [`From`] implementation, but more data than [`EpisodeNoId`] has is needed
    pub fn new(value: &'a EpisodeNoId, pod_id: PodcastDBId) -> Self {
        Self {
            pod_id,
            title: &value.title,
            url: &value.url,
            guid: &value.guid,
            description: &value.description,
            pubdate: value.pubdate,
            duration: value.duration,
            played: false,
            hidden: false,
            last_position: Some(0),
            image_url: value.image_url.as_deref(),
        }
    }

    /// Insert the current [`EpisodeDBInsertable`] into the `episodes` table
    #[inline]
    pub fn insert_episode(&self, con: &Connection) -> Result<usize, rusqlite::Error> {
        let mut stmt = con.prepare_cached(indoc! {"
            INSERT INTO episodes (podcast_id, title, url, guid,
                description, pubdate, duration, played, hidden, last_position, image_url)
            VALUES (:podid, :title, :url, :guid, :description, :pubdate, :duration, :played, :hidden, :last_position, :image_url);
        "})?;
        stmt.execute(named_params![
            ":podid": self.pod_id,
            ":title": self.title,
            ":url": self.url,
            ":guid": self.guid,
            ":description": self.description,
            ":pubdate": self.pubdate.map(|v| v.timestamp()),
            ":duration": self.duration,
            ":played": self.played,
            ":hidden": self.hidden,
            ":last_position": self.last_position,
            ":image_url": self.image_url,
        ])
    }

    /// Update a given id with the current [`EpisodeDBInsertable`] in the `episodes` table
    ///
    /// This function does NOT update:
    /// - `podcast_id`
    /// - `hidden`
    /// - `played`
    /// - `last_position`
    #[inline]
    pub fn update_episode(
        &self,
        id: PodcastDBId,
        con: &Connection,
    ) -> Result<usize, rusqlite::Error> {
        let mut stmt = con.prepare_cached(indoc! {"
            UPDATE episodes SET title = :title, url = :url,
                guid = :guid, description = :description, pubdate = :pubdate,
            duration = :duration, image_url = :image_url WHERE id = :epid;
        "})?;
        stmt.execute(named_params![
            ":title": self.title,
            ":url": self.url,
            ":guid": self.guid,
            ":description": self.description,
            ":pubdate": self.pubdate.map(|v| v.timestamp()),
            ":duration": self.duration,
            ":image_url": self.duration,
            ":epid": id,
        ])
    }
}

/// Delete a episode by id
///
/// This also deletes all associated files (not removing the actual files)!
#[allow(dead_code)]
pub fn delete_episode(id: PodcastDBId, con: &Connection) -> Result<usize, rusqlite::Error> {
    let mut stmt = con.prepare_cached("DELETE FROM episodes WHERE id = ?;")?;
    stmt.execute(params![id])
}
